package com.OnlineHouseLeaseSystem.common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.OnlineHouseLeaseSystem.database.DBManager;

public class ComplaintManager {
	private static final String USER_ID = "user_id";
	private static final String COMPLAINT_ID = "complaint_id";
	private static final String COMPLAINT = "complaint";
	private static final String REPLY = "reply";
	
	private final static String LOCK_TABLE = "LOCK TABLE complaint IN EXCLUSIVE MODE; ";
	
	private final static String GET_MAX_COMPLAINT_ID = "SELECT MAX(complaint_id) FROM complaint";
	private final static String INSERT_COMPLAINT_HEAD = "INSERT INTO complaint VALUES ( '";
    private final static String INSERT_COMPLAINT_FOOT = "' ) ";
    private final static String GET_USER_COMPLAINT = "SELECT * FROM complaint" +
													 " WHERE complaint.user_id='";
    private final static String GET_ALL_COMPLAINT = "SELECT * FROM complaint ORDER BY complaint_id DESC";
    private final static String DELETE_COMPLAINT = "DELETE FROM complaint WHERE complaint_id='";
    private final static String GET_COMPLAINT = "SELECT * FROM complaint WHERE complaint_id='";
    private final static String UPDATE_COMPLAINT_REPLY = "UPDATE complaint SET reply='";
    
    public static void updateComplaintReply(int complaint_id,String reply)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_COMPLAINT_REPLY + reply + "' WHERE complaint_id='" + complaint_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static Complaint getComplaint(int complaint_id)
    {
    	Complaint complaint = new Complaint();
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_COMPLAINT + complaint_id + "'");
            result.next();
            complaint.setUser_id(result.getInt(USER_ID));
            complaint.setComplaint_id(result.getInt(COMPLAINT_ID));
            complaint.setComplaint(result.getString(COMPLAINT));
            complaint.setReply(result.getString(REPLY));
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return complaint;
    }
    
    public static void deleteComplaint(int complaint_id)
	{
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_COMPLAINT + complaint_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
    
    public static ArrayList getAllComplaint()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_ALL_COMPLAINT);
    		while(result.next())
    		{
				Complaint complaint = new Complaint();
				complaint.setUser_id(result.getInt(USER_ID));
				complaint.setComplaint_id(result.getInt(COMPLAINT_ID)); 
				complaint.setComplaint(result.getString(COMPLAINT)); 
				complaint.setReply(result.getString(REPLY));
                list.add(complaint);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static ArrayList getUserComplaint(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_USER_COMPLAINT + user_id + "' ORDER BY complaint_id DESC");
    		while(result.next())
    		{
    			Complaint complaint = new Complaint();
    			complaint.setUser_id(result.getInt(USER_ID));
    			complaint.setComplaint_id(result.getInt(COMPLAINT_ID)); 
    			complaint.setComplaint(result.getString(COMPLAINT)); 
    			complaint.setReply(result.getString(REPLY));
                list.add(complaint);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static int getComplaint_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE+GET_MAX_COMPLAINT_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }
    
    public static void insertNewComplaint(int user_id,String complaint)
    {
    	int complaint_id = getComplaint_idForNew();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(INSERT_COMPLAINT_HEAD +
    				user_id + "','" + complaint_id + "','" + complaint + "','" + INSERT_COMPLAINT_FOOT);
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
}
